package com.bt.lms.dao.servise.impl;

import com.bt.lms.bo.StudentList;
import com.bt.lms.constant.BOConstant;
import com.bt.lms.dao.servise.StuStatusInfoDao;
import com.bt.lms.entity.*;
import com.bt.lms.entity.page.Page;
import com.bt.lms.utils.DriverUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 查看学员详情dao实现
 *
 * @author 王波
 */
public class StuStatusInfoDaoImpl implements StuStatusInfoDao {
    @Override
    public StuBasicInfo selectBasicInfo(String sql, StuBasicInfo stuBasicInfo, Long id) throws SQLException {
        //获取连接
        Connection conn = DriverUtils.Driver();
        //创建执行对象
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);
        //执行sql语句
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            String code = rs.getString("code");
            String name = rs.getString("name");
            int sex = rs.getInt("sex");
            String birthDate = rs.getString("birth_date");
            String aNative = rs.getString("native");
            String nation = rs.getString("nation");
            String birthplace = rs.getString("birthplace");
            int idType = rs.getInt("id_type");
            String idCard = rs.getString("id_card");
            int maritalStatus = rs.getInt("marital_status");
            int politicsStatus = rs.getInt("politics_status");
            //带走
            stuBasicInfo.setId(id);
            stuBasicInfo.setNo(code);
            stuBasicInfo.setName(name);
            stuBasicInfo.setSex(sex);
            //String 转localDate
            LocalDate birthDay = LocalDate.parse(birthDate, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            stuBasicInfo.setBirthTime(birthDay);
            stuBasicInfo.setNativePlace(aNative);
            stuBasicInfo.setNation(nation);
            stuBasicInfo.setBirthplace(birthplace);
            stuBasicInfo.setIdType(idType);
            stuBasicInfo.setIdCard(idCard);
            stuBasicInfo.setMaritalStatus(maritalStatus);
            stuBasicInfo.setPoliticsStatus(politicsStatus);

        }

        //关闭资源
        ps.close();
        conn.close();
        return stuBasicInfo;
    }

    @Override
    public StuBasicInfo selectPictureInfo(String sql, StuBasicInfo stuBasicInfo, Long id) throws SQLException {
        Connection conn = DriverUtils.Driver();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            String picturePath = rs.getString("picture_path");
            //带走
            stuBasicInfo.setPicture(picturePath);
        }

        //关闭资源
        ps.close();
        conn.close();
        return stuBasicInfo;
    }

    @Override
    public StuBasicInfo selectStuExtend(String sql, StuBasicInfo stuBasicInfo, Long id) throws SQLException {
        Connection conn = DriverUtils.Driver();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);
        ResultSet extendRS = ps.executeQuery();

        if (extendRS.next()) {
            String englishName = extendRS.getString("english_name");
            String namePhoneticize = extendRS.getString("name_phoneticize");
            String formerName = extendRS.getString("former_name");
            int faith = extendRS.getInt("faith");
            String nationality = extendRS.getString("nationality");
            int alien = extendRS.getInt("alien");
            int physicalCondition = extendRS.getInt("physical_condition");
            int bloodType = extendRS.getInt("blood_type");
            String startIdCardValidity = extendRS.getString("start_id_card_validity");
            String stopIdCardValidity = extendRS.getString("stop_id_card_validity");
            long stuBasicInfoId = extendRS.getLong("stu_basic_info_id");//学员的id

            //带走
            stuBasicInfo.setEnglishName(englishName);
            stuBasicInfo.setNamePhoneticize(namePhoneticize);
            stuBasicInfo.setFormerName(formerName);
            stuBasicInfo.setFaith(faith);
            stuBasicInfo.setNationality(nationality);
            stuBasicInfo.setIsAlien(alien);
            stuBasicInfo.setPhysicalCondition(physicalCondition);
            stuBasicInfo.setBloodType(bloodType);
            //string转localDate
            LocalDate startDate = LocalDate.parse(startIdCardValidity, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            stuBasicInfo.setStartIDCardValidity(startDate);
            //string转localDate
            LocalDate stopDate = LocalDate.parse(stopIdCardValidity, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            stuBasicInfo.setStopIDCardValidity(stopDate);
            stuBasicInfo.setStuStatusInfoID(stuBasicInfoId);
        }
        //关闭资源
        ps.close();
        conn.close();
        return stuBasicInfo;
    }

    @Override
    public StuBasicInfo selectFamilyMember(String sql, StuBasicInfo stuBasicInfo, Long id) throws SQLException {

        //获取连接
        Connection conn = DriverUtils.Driver();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);
        List<FamilyMemberInfo> list = new ArrayList<>();
        //执行
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            //家庭成员信息
            FamilyMemberInfo familyMemberInfo = new FamilyMemberInfo();
            String relation = rs.getString("relation");
            String name = rs.getString("name");
            String birthday = rs.getString("birthday");
            String workUnit = rs.getString("work_unit");
            String job = rs.getString("job");
            String phone = rs.getString("phone");
            String nation = rs.getString("nation");
            int nationality = rs.getInt("nationality");
            String healthCondition = rs.getString("health_condition");
            String duty = rs.getString("duty");
            String dutyRank = rs.getString("duty_rank");
            String email = rs.getString("email");
            int isEmergencyContact = rs.getInt("isEmergency_contact");

            //让家庭成员带走
            familyMemberInfo.setRelation(relation);
            familyMemberInfo.setName(name);
            familyMemberInfo.setBirthday(LocalDate.parse(birthday));
            familyMemberInfo.setWorkUnit(workUnit);
            familyMemberInfo.setJob(job);
            familyMemberInfo.setPhone(phone);
            familyMemberInfo.setNation(nation);
            familyMemberInfo.setNationality(nationality);
            familyMemberInfo.setHealthCondition(healthCondition);
            familyMemberInfo.setDuty(duty);
            familyMemberInfo.setDutyRank(dutyRank);
            familyMemberInfo.setEmail(email);
            familyMemberInfo.setIsEmergencyContact(isEmergencyContact);
            //让学员信息带走家庭成员信息
            list.add(familyMemberInfo);
        }
        stuBasicInfo.setFamilyMemberInfos(list);


        //关闭资源
        ps.close();
        conn.close();
        return stuBasicInfo;
    }

    @Override
    public StuBasicInfo selectStuStatusInfo(String sql, StuBasicInfo stuBasicInfo, Long id) throws SQLException {
        Connection conn = DriverUtils.Driver();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);
        ResultSet rs = ps.executeQuery();
        //学籍信息
        StuStatusBasicInfo stuStatusBasicInfo = new StuStatusBasicInfo();
        if (rs.next()) {
            String stuNo = rs.getString("stu_no");
            String name = rs.getString("name");
            int idType = rs.getInt("id_type");
            String idCard = rs.getString("id_card");
            String startSchoolDate = rs.getString("start_school_date");
            String schoolSystem = rs.getString("school_system");
            String className = rs.getString("class_name");
            String enpPlan = rs.getString("enp_plan");
            String learningCenter = rs.getString("learning_center");
            String specialty = rs.getString("specialty");
            int studentState = rs.getInt("student_state");

            //带走
            stuStatusBasicInfo.setStuNo(stuNo);
            stuStatusBasicInfo.setName(name);
            stuStatusBasicInfo.setiDType(idType);
            stuStatusBasicInfo.setiDCard(idCard);
            stuStatusBasicInfo.setEnterSchoolTime(LocalDate.parse(startSchoolDate));
            stuStatusBasicInfo.setLengthOfSchooling(schoolSystem);
            stuStatusBasicInfo.setClassName(className);
            stuStatusBasicInfo.setEnrollPlan(enpPlan);
            stuStatusBasicInfo.setLearningCenter(learningCenter);
            stuStatusBasicInfo.setSpecialty(specialty);
            stuStatusBasicInfo.setCurrentState(studentState);

        }
        //带走
        stuBasicInfo.setStudentStatusBasicInfo(stuStatusBasicInfo);


        //关闭资源
        ps.close();
        conn.close();
        return stuBasicInfo;
    }

    @Override
    public StuBasicInfo selectRegisterInfo(String sql, StuBasicInfo stuBasicInfo, Long id) throws SQLException {
        //获取到学籍信息
        StuStatusBasicInfo stuStatusBasicInfo = stuBasicInfo.getStudentStatusBasicInfo();

        //获取连接
        Connection conn = DriverUtils.Driver();
        //执行sql语句
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);
        List<RegisterInfo> list = new ArrayList<>();
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            //注册信息
            RegisterInfo registerInfo = new RegisterInfo();
            int registerState = rs.getInt("register_state");
            String registerDate = rs.getString("register_date");
            String gotoDate = rs.getString("goto_date");
            String noRegisterCause = rs.getString("no_register_cause");
            String noRegisterGoto = rs.getString("no_register_goto");
            int isvacate = rs.getInt("isvacate");
            String schoolYear = rs.getString("school_year");
            String semester = rs.getString("semester");
            String classroom = rs.getString("classroom");
            //让注册信息带走
            registerInfo.setState(registerState);
            registerInfo.setDate(LocalDate.parse(registerDate));
            registerInfo.setGotoDate(LocalDate.parse(gotoDate));
            registerInfo.setNotRegisterCause(noRegisterCause);
            registerInfo.setNotRegisterGo(noRegisterGoto);
            registerInfo.setIsVacate(isvacate);
            registerInfo.setSchoolYear(schoolYear);
            registerInfo.setSemester(semester);
            registerInfo.setClassroom(classroom);
            list.add(registerInfo);
        }
        //学籍信息带走注册信息
        stuStatusBasicInfo.setRegisterInfo(list);


        //关闭资源
        ps.close();
        conn.close();
        return stuBasicInfo;
    }

    @Override
    public StuBasicInfo selectTransferInfo(String sql, StuBasicInfo stuBasicInfo, Long id) throws SQLException, ParseException {
        //获取到学籍信息
        StuStatusBasicInfo stuStatusBasicInfo = stuBasicInfo.getStudentStatusBasicInfo();

        Connection conn = DriverUtils.Driver();
        //执行sql语句
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);
        ResultSet rs = ps.executeQuery();
        //异动信息集合
        List<TransferInfo> list = new ArrayList<>();
        while (rs.next()) {
            //学籍异动信息
            TransferInfo transferInfo = new TransferInfo();
            int transferType = rs.getInt("transfer_type");
            int beforeState = rs.getInt("before_state");
            String transferCause = rs.getString("transfer_cause");
            int laterState = rs.getInt("later_state");
            String transferDate = rs.getString("transfer_date");
            String explain = rs.getString("transfer_explain");
            //异动信息带走
            transferInfo.setType(transferType);
            transferInfo.setBeforeState(beforeState);
            transferInfo.setCause(transferCause);
            transferInfo.setLaterState(laterState);
            //string转date
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date parse = simpleDateFormat.parse(transferDate);
            transferInfo.setDate(parse);
            transferInfo.setExplain(explain);
            //放进集合里
            list.add(transferInfo);
        }
        stuStatusBasicInfo.setTransferInfo(list);


        //关闭资源
        ps.close();
        conn.close();
        return stuBasicInfo;
    }

    @Override
    public StuBasicInfo selectEmploymentInfo(String sql, StuBasicInfo stuBasicInfo, Long id) throws SQLException {
        //获取到学籍信息
        StuStatusBasicInfo stuStatusBasicInfo = stuBasicInfo.getStudentStatusBasicInfo();
        //就业信息
        EmploymentInfo employmentInfo = new EmploymentInfo();
        //获取连接
        Connection conn = DriverUtils.Driver();
        //执行sql语句
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            String firmName = rs.getString("firm_name");
            String firmSite = rs.getString("firm_site");
            String protocolConfirmDate = rs.getString("protocol_confirm_date");
            String registerDate = rs.getString("register_date");
            int firmType = rs.getInt("firm_type");
            String firmDescribe = rs.getString("firm_describe");
            //就业信息带走
            employmentInfo.setFirmName(firmName);
            employmentInfo.setFirmSite(firmSite);
            employmentInfo.setProtocolSignedDate(LocalDate.parse(protocolConfirmDate));
            employmentInfo.setRegisterDate(LocalDate.parse(registerDate));
            employmentInfo.setFirmType(firmType);
            employmentInfo.setFirmDescribe(firmDescribe);
        }
        stuStatusBasicInfo.setEmploymentInfo(employmentInfo);


        //关闭资源
        ps.close();
        conn.close();
        return stuBasicInfo;
    }

    @Override
    public Page<StudentList> selectPagingQuery(String sql, String count, Page<StudentList> page) throws SQLException {
        Connection conn = DriverUtils.Driver();
        PreparedStatement ps = conn.prepareStatement(sql);
        Integer current = page.getCurrent();
        Integer size = page.getSize();
        ps.setInt(1, current);
        ps.setInt(2, size);

        ResultSet rs = ps.executeQuery();
        Page<StudentList> studentListPage = new Page<>();
        List<StudentList> studentLists = new ArrayList<>();
        while (rs.next()) {
            StudentList studentList = new StudentList();
            //序号
            long no = rs.getLong("id");
            //学员编号
            String stuNo = rs.getString("req_code");
            //姓名
            String name = rs.getString("name");
            //性别
            String sex = rs.getString("gender");
            //学习中心
            String learnCenter = rs.getString("studyName");
            //专业
            String major = rs.getString("majorName");
            //招生计划
            String enpName = rs.getString("plan_name");
            //班级名称
            //手机号码
            String phone = rs.getString("mobile_phone");


            //带走
            studentList.setNo(no);
            studentList.setStuNo(stuNo);
            studentList.setName(name);
            studentList.setSex(sex);
            studentList.setLearnCenter(learnCenter);
            studentList.setMajor(major);
            studentList.setEnp(enpName);
//            studentList.setClassName(className);
            studentList.setPhone(phone);
//            studentList.setState(state);
            studentLists.add(studentList);
        }
        studentListPage.setRecords(studentLists);

        PreparedStatement ps1 = conn.prepareStatement(count);
        ResultSet rs1 = ps1.executeQuery();
        rs1.next();
        long aLong = rs1.getLong(1);
        studentListPage.setTotal(aLong);


        return studentListPage;
    }

    @Override
    public Page<StudentList> infoQuery( String sql, String sqlCount, Page<StudentList> page) throws SQLException {
        Connection conn = DriverUtils.Driver();
        PreparedStatement ps = conn.prepareStatement(sql);
        //
        Page<StudentList> studentListPage = new Page<>();
        //总记录数
        PreparedStatement ps1 = conn.prepareStatement(sqlCount);
        ps1.setInt(1, BOConstant.DELETED_FALSE);
        ResultSet rs1 = ps1.executeQuery();
        rs1.next();
        long aLong = rs1.getLong(1);
        studentListPage.setTotal(aLong);
        Integer current = page.getCurrent();
        Integer size = page.getSize();
        String s = String.valueOf(current);


        if (s.matches("^[0-9]*$")) {
            if (current == 1){
                ps.setInt(1, BOConstant.DELETED_FALSE);
                ps.setInt(2, 0);
                ps.setInt(3, size);
            }else {
                ps.setInt(1, BOConstant.DELETED_FALSE);
                int i = size*(current - 1);
                ps.setInt(2, i);
                ps.setInt(3, size);
            }
        }


        ResultSet rs = ps.executeQuery();

        List<StudentList> studentLists = new ArrayList<>();
        while (rs.next()) {
            StudentList studentList = new StudentList();
            //序号
            long no = rs.getLong("id");
            //学员编号
            String stuNo = rs.getString("req_code");
            //姓名
            String name = rs.getString("name");
            //性别
            String sex = rs.getString("gender");
            //学习中心
//            String learnCenter = rs.getString("studyName");
            //专业
//            String major = rs.getString("majorName");
            //招生计划
//            String enpName = rs.getString("plan_name");
            //班级名称
            //手机号码
            String phone = rs.getString("mobile_phone");


            //带走
            studentList.setNo(no);
            studentList.setStuNo(stuNo);
            studentList.setName(name);
            studentList.setSex(sex);
//            studentList.setLearnCenter(learnCenter);
//            studentList.setMajor(major);
//            studentList.setEnp(enpName);
//            studentList.setClassName(className);
            studentList.setPhone(phone);
//            studentList.setState(state);
            studentLists.add(studentList);
        }
        studentListPage.setRecords(studentLists);



        return studentListPage;
    }
}
